using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

using umlungu.engineX.dataX;

/*	-----------------------------------------------------------------------	
	Copyright:	umlungu consulting (pty) ltd
	Author:		Alan Benington
	Started:	2007-05-27	
	Status:		redev	
	Version:	2.0.0
	Build:		20071226
	License:	GNU General Public License
	-----------------------------------------------------------------------	*/
	
/*	----------------------------------------------------------------------------------------------------------------------------------------------	
	Note re EngineX.PassportX:
	------------------------------------
	The refactoring mentioned below is intended to result in a code base that can be migrated into the EngineX 
	framework as follows:
	- Base classes migrated to the EngineX.PassportX namespace.
	- Web Service classes migrated to the EngineX.PassportServiceX namespace.
	- Web service classes can remain in an implementation class (eg NashuaMobile.Gatekeeper)
	Note: Once this is completed the Gatekeeper code will become dependant on PassportX base classes.
		  If this is not desired, a fork in the codebase will be created at that stage.
	----------------------------------------------------------------------------------------------------------------------------------------------	*/
	
/*	-----------------------------------------------------------------------
	Development Notes:
	==================
	20070220:	This base class was taken from the previous GateKeeper 
				class and split into two (ie this and GateKeeperResult)
				Class hierarchy as follows:
					GateKeeperBase _
									|_ GateKeeperResult
	20070425:	Fixed getSqlString to be safe when column is not string
	20070527:	- Starting point from NMGatekeeper.2.0.2.
				Class hierarchy now becomes:
					_x_passport_
								|_x_result (could later utilise engineX.x_status)
	20071226:	Added support for multiple databases using DataX database
				provider manager in the umlungu.engineX.dataX namespace.
-----------------------------------------------------------------------	*/
	
namespace umlungu.engineX.passportX {
	/// <summary>Description of the enumeration</summary>
	public enum SqlType {
		/// <summary>A string</summary>
	    String = 0,
		/// <summary>An integer</summary>
	    Integer = 1,
		/// <summary>A boolean string ie 'yes' or 'no'</summary>
	    YesNo = 2,
		/// <summary>A guid ie the token</summary>
	    Guid = 3
	}
	/// <summary>
	/// Base class for Gatekeeper classes.
	/// </summary>
	public class x_passport : x_result {
		#region Visible properties
		private string dsn;
		/// <summary>GateKeeper database connection string</summary>
		/// <value>Standard MsSQL connection string</value>
		public string DSN {
			get { return dsn; }
			set { dsn = value; }
		}

		private DataXManager dataXManager;
		/// <summary>Database manager object</summary>
		/// <value>IDataXManager manager object</value>
		public DataXManager DataXManager {
			get { return dataXManager; }
			//set { dataXManager = value; }
		}

		private IDbConnection dbConnection;
		/// <summary>Database connection object</summary>
		/// <value>MsSQL connection object</value>
		public IDbConnection DBConnection {
			get { return dbConnection; }
			//set { dbConnection = value; }
		}
		private IDbCommand dbCommand;
		/// <summary>Database connection object</summary>
		/// <value>MsSQL connection object</value>
		public IDbCommand DBCommand {
			get { return dbCommand; }
			//set { dbCommand = value; }
		}
		private IDataReader dbReader;
		/// <summary>Database data reader object</summary>
		/// <value>MsSQL DataReader object</value>
		public IDataReader DBReader {
			get { return dbReader; }
			//set { dbReader = value; }
		}
		#endregion

		#region Constructors/Destructors
		/// <overloads>Constructor</overloads>
		/// <summary>Default constructor</summary>
		public x_passport() : base() {
			dataXManager =  new DataXManager(DataProviderType.SqlServer);
		}
		/// <summary>Normal constructor</summary>
		/// <param name="DSN">DB connection string</param>
		protected x_passport(string DSN) : base() {
			dsn = DSN;
			dataXManager = new DataXManager(DataProviderType.SqlServer, dsn);
		}
		/// <summary>Normal constructor</summary>
		/// <param name="DSN">DB connection string</param>
		/// <param name="id">the object identifier</param>
		protected x_passport(string DSN, string id) : base(id) {
			dsn = DSN;
			dataXManager = new DataXManager(DataProviderType.SqlServer, dsn);
		}
		/// <summary>Normal constructor</summary>
		/// <param name="dbType">The data provider type</param>
		/// <param name="DSN">DB connection string</param>
		protected x_passport(DataProviderType dbType, string DSN) : base() {
			dsn = DSN;
			dataXManager = new DataXManager(dbType, dsn);
		}
		/// <summary>Normal constructor</summary>
		/// <param name="dbType">The data provider type</param>
		/// <param name="DSN">DB connection string</param>
		/// <param name="id">the object identifier</param>
		protected x_passport(DataProviderType dbType, string DSN, string id) : base(id) {
			dsn = DSN;
			dataXManager = new DataXManager(dbType, dsn);
		}
		#endregion

		#region Public methods
		/// <summary>Connect to the database using the connection string</summary>
		public IDbConnection Connect() {
			dbConnection = dataXManager.Connect();
			return dbConnection;
		}
		/// <summary>Create a command to be executed with connection</summary>
		public IDbCommand Command(string storedproc) {
			dbCommand = dataXManager.GetCommand(storedproc);
			return dbCommand;
		}
		/// <overloads>Open a reader onto a dataset - ie execute a stored proc and get a dataset</overloads>
		/// <summary></summary>
		public IDataReader Reader() {
			dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dbReader;
		}
		/// <summary></summary>
		public IDataReader Reader(CommandBehavior type) {
			dbReader = dbCommand.ExecuteReader(type);
			return dbReader;
		}
		/// <summary>Read from the dataset</summary>
		public bool Read() {
			return dbReader.Read();
		}
		/// <summary>Execute a stored proc that doesn't return a dataset</summary>
		public int Execute() {
			return dbCommand.ExecuteNonQuery();
		}
		/// <summary>Execute a stored proc returns a single value</summary>
		public object ExecuteScalar() {
			return dbCommand.ExecuteScalar();
		}
		/// <summary>Read from the dataset</summary>
		public void Next() {
			dbReader.NextResult();
		}
		#endregion
		
		#region Protected methods
		/// <overloads>Adds an parameter to a stored procedure call</overloads>
		/// <summary>Integer parameter</summary>
		protected IDataParameter AddSQLParameter(int parval, string parname) {
			return AddSQLParameter(dbCommand, parval, parname, false);
		}
		/// <summary>Integer parameter</summary>
		protected IDataParameter AddSQLParameter(SqlCommand parcmd, int parval, string parname) {
			return AddSQLParameter(parcmd, parval, parname, false);
		}
		/// <summary>Integer parameter</summary>
		protected IDataParameter AddSQLParameter(string parname) {
			return AddSQLParameter(dbCommand, parname);
		}
		/// <summary>Integer parameter</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, string parname) {
			IDataParameter par = dataXManager.CreateParameter(parname, DbType.Int32);
			par.Direction = ParameterDirection.Output;
			parcmd.Parameters.Add(par);
			return(par);
		}
		/// <summary>Integer parameter with output flag</summary>
		protected IDataParameter AddSQLParameter(int parval, string parname, bool isout) {
			return AddSQLParameter(dbCommand, parval, parname, isout);
		}
		/// <summary>Integer parameter with output flag</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, int parval, string parname, bool isout) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, DbType.Int32);
				if (isout)
					par.Direction = ParameterDirection.Output;
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <summary>Integer parameter with direction</summary>
		protected IDataParameter AddSQLParameter(int parval, string parname, ParameterDirection dirtn) {
			return AddSQLParameter(dbCommand, parval, parname, dirtn);
		}
		/// <summary>Integer parameter with direction</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, int parval, string parname, ParameterDirection dirtn) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, DbType.Int32);
				par.Direction = ParameterDirection.Output;
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <summary>String parameter</summary>
		protected IDataParameter AddSQLParameter(string parval, string parname, int parlen) {
			return AddSQLParameter(dbCommand, parval, parname, parlen, false);
		}
		/// <summary>String parameter</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, string parval, string parname, int parlen) {
			return AddSQLParameter(parcmd, parval, parname, parlen, false);
		}
		/// <summary>String parameter</summary>
		protected IDataParameter AddSQLParameter(string parname, int parlen, bool isout) {
			return AddSQLParameter(dbCommand, parname, parlen, isout);
		}
		/// <summary>String parameter</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, string parname, int parlen, bool isout) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, DbType.String, parlen);
				if (isout)
					par.Direction = ParameterDirection.Output;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <summary>String parameter</summary>
		protected IDataParameter AddSQLParameter(string parval, string parname, int parlen, bool isout) {
			return AddSQLParameter(dbCommand, parval, parname, parlen, isout);
		}
		/// <summary>String parameter</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, string parval, string parname, int parlen, bool isout) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, DbType.String, parlen);
				par.Direction = (isout)? ParameterDirection.Output : ParameterDirection.Input;
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/*
		/// <summary>Spefified type of  parameter</summary>
		protected IDataParameter AddSQLParameter(string parval, string parname, DbType type) {
			return AddSQLParameter(dbCommand, parval, parname, type);
		}
		/// <summary>Spefified type of  parameter</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, string parval, string parname, DbType type) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, type);
				par.Direction = ParameterDirection.Output;
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		*/
		/// <summary>Specified type of  parameter</summary>
		protected IDataParameter AddSQLParameter(object parval, string parname, DbType type) {
			return AddSQLParameter(dbCommand, parval, parname, type);
		}
		/// <summary>Specified type of  parameter</summary>
		protected IDataParameter AddSQLParameter(IDbCommand parcmd, object parval, string parname, DbType type) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, type);
				par.Direction = ParameterDirection.InputOutput;
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return (par);
			} catch {
				return (null);
			}
		}
		protected IDataParameter AddSQLParameter(DateTime parval, string parname, DbType type) {
			try {
				IDataParameter par = dataXManager.CreateParameter(parname, type);
				par.Direction = ParameterDirection.Input;
				par.Value = parval;
				dbCommand.Parameters.Add(par);
				return (par);
			}
			catch {
				return (null);
			}
		}
		
		
		/// <overloads>Adds an element</overloads>
		/// <summary>Adds a column value using the local data reader</summary>
		protected void AddElement(string name, string thiscol) {
			_AddElement(name, getSqlString(dbReader, thiscol));
		}
		/// <summary></summary>
		protected void AddElement(string name, string thiscol,DbType thistyp) {
			_AddElement(name, GetValue(dbReader, thiscol, thistyp));
		}
		/// <summary></summary>
		protected void AddElement(string parent, string name, string thiscol) {
			_AddElement(parent, name, getSqlString(dbReader, thiscol));
		}
		/// <summary></summary>
		protected void AddElement(string parent, string name, string thiscol, DbType thistyp) {
			_AddElement(parent, name, GetValue(dbReader, thiscol, thistyp));
		}
		/// <summary></summary>
		protected void AddElement(XmlNode parent, string name, string thiscol) {
			XmlNode child = _AddElement(parent, name);
			child.InnerText = getSqlString(dbReader, thiscol);
		}
		
		/// <overloads>Adds a column value using a data reader</overloads>
		/// <summary></summary>
		protected void AddElement(string name, IDataReader thisrdr, string thiscol) {
			_AddElement(name, getSqlString(thisrdr, thiscol));
		}
		/// <summary></summary>
		protected void AddElement(string name, IDataReader thisrdr, string thiscol, DbType thistyp) {
			_AddElement(name, GetValue(thisrdr, thiscol, thistyp));
		}
		/// <summary></summary>
		protected void AddElement(string parent, string name, IDataReader thisrdr, string thiscol) {
			_AddElement(parent, name, getSqlString(thisrdr, thiscol));
		}
		/// <summary></summary>
		protected void AddElement(string parent, string name, IDataReader thisrdr, string thiscol, DbType thistyp) {
			_AddElement(parent, name, GetValue(thisrdr, thiscol, thistyp));
		}
		
		/// <overloads>Adds an attribute</overloads>
		/// <summary>Adds a column value using the local data reader</summary>
		protected void AddAttribute(string name, string thiscol) {
			_AddAttribute(name, getSqlString(dbReader, thiscol));
		}
		/// <summary></summary>
		protected void AddAttribute(string name, string thiscol, DbType thistyp) {
			_AddAttribute(name, GetValue(dbReader, thiscol, thistyp));
		}
		/// <summary></summary>
		protected void AddAttribute(string parent, string name, string thiscol) {
			_AddAttribute(parent, name, getSqlString(dbReader, thiscol));
		}
		/// <summary></summary>
		protected void AddAttribute(string parent, string name, string thiscol, DbType thistyp) {
			_AddAttribute(parent, name, GetValue(dbReader, thiscol, thistyp));
		}
		
		/// <overloads>Adds a column value using a data reader</overloads>
		/// <summary></summary>
		protected void AddAttribute(string name, IDataReader thisrdr, string thiscol) {
			_AddAttribute(name, getSqlString(thisrdr, thiscol));
		}
		/// <summary></summary>
		protected void AddAttribute(string name, IDataReader thisrdr, string thiscol, DbType thistyp) {
			_AddAttribute(name, GetValue(thisrdr, thiscol, thistyp));
		}
		/// <summary></summary>
		protected void AddAttribute(string parent, string name, IDataReader thisrdr, string thiscol) {
			_AddAttribute(parent, name, getSqlString(thisrdr, thiscol));
		}
		/// <summary></summary>
		protected void AddAttribute(string parent, string name, IDataReader thisrdr, string thiscol, DbType thistyp) {
			_AddAttribute(parent, name, GetValue(thisrdr, thiscol, thistyp));
		}
		
		/// <overloads>Adds a column value to an element using the local data reader</overloads>
		/// <summary></summary>
		protected void AddAttribute(XmlElement elem, string name, string thiscol) {
			elem.SetAttribute(name, getSqlString(dbReader, thiscol));
		}
		/// <summary></summary>
		protected void AddAttribute(XmlElement elem, string name, string thiscol, DbType thistyp) {
			elem.SetAttribute(name, GetValue(dbReader, thiscol, thistyp));
		}
		
		/// <overloads>Adds an attribute to a node of a document</overloads>
		/// <summary>Adds from a field in a recordset</summary>
		protected bool AddAttr(XmlDocument xmlThis, XmlNode xnThis, IDataReader rdrThis, string attname, string fldname) {
			try {
				XmlAttribute xaThis = xmlThis.CreateAttribute(attname);
				xaThis.InnerText = rdrThis.GetValue(rdrThis.GetOrdinal(fldname)).ToString();
				xnThis.Attributes.Append(xaThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <overloads>Adds an attribute to a node of a document</overloads>
		/// <summary>Adds from a field in a recordset</summary>
		protected bool AddAttr(XmlNode xnThis, IDataReader rdrThis, string attname, string fldname) {
			return AddAttr(this, xnThis, rdrThis, attname, fldname);
		}
		/// <summary>Adds by supplied name and value</summary>
		protected bool AddAttr(XmlDocument xmlThis, XmlNode xnThis, string attname, string attval) {
			try {
				XmlAttribute xaThis = xmlThis.CreateAttribute(attname);
				xaThis.InnerText = attval;
				xnThis.Attributes.Append(xaThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>Adds by supplied name and value</summary>
		protected bool AddAttr(XmlNode xnThis, string attname, string attval) {
			return AddAttr(this, xnThis, attname, attval);
		}
		
		/// <overloads>A safe method of getting a string from a IDataReader column when the value could be null</overloads>
		/// <summary>Uses local DataReader</summary>
		protected string GetValue(string thiscol) {
			return getSqlString(dbReader, thiscol);
		}
		/// <summary>String type assumed</summary>
		protected string GetValue(IDataReader thisrdr, string thiscol) {
			return getSqlString(thisrdr, thiscol);
		}
		/// <summary>Type specified</summary>
		protected string GetValue(string thiscol, DbType thistyp) {
			return GetValue(dbReader, thiscol, thistyp);
		}
		/// <summary>Type specified</summary>
		protected string GetValue(IDataReader thisrdr, string thiscol, DbType thistyp) {
			string thisval;
			switch (thistyp) {
				case DbType.Guid:		thisval = getSqlGuid(thisrdr, thiscol);		break;
				case DbType.Boolean:	thisval = getSqlYesNo(thisrdr, thiscol);	break;
				case DbType.Int32:		thisval = getSqlInteger(thisrdr, thiscol);	break;
				case DbType.String:	
				default:				thisval = getSqlString(thisrdr, thiscol);	break;
			}
			return thisval;
		}
		/// <summary>Parameter value - string</summary>
		protected string GetValue(IDataParameter thispar) {
			return (thispar.Value == null)? "" : thispar.Value.ToString();
		}
		/// <summary>Parameter value - string</summary>
		protected int GetValue(string thiscol, int defvalue) {
			string thisval = getSqlString(dbReader, thiscol);
			return (thisval == "")? defvalue : Convert.ToInt32(thisval);
		}
		/// <summary>Parameter value - int</summary>
		protected int GetValue(IDataParameter thispar, int defvalue) {
			return (thispar.Value == null)? defvalue : Convert.ToInt32(thispar.Value.ToString());
		}
		
		/// <overloads>Tests a value of a column for null condition</overloads>
		/// <summary>Local DataReader></summary>
		protected bool Test(string thiscol) {
			return !dbReader.IsDBNull(dbReader.GetOrdinal(thiscol));
		}
		/// <summary>Arbitary DataReader></summary>
		protected bool Test(IDataReader thisrdr, string thiscol) {
			return !thisrdr.IsDBNull(thisrdr.GetOrdinal(thiscol));
		}
		/// <summary>Tests IDataParameter for non null result</summary>
		protected bool Test(IDataParameter thispar) {
			return (thispar.Value != DBNull.Value) && (thispar.Value.ToString() != "");
		}
		#endregion
		
		#region Private methods
		/// <summary>
		/// A safe method of getting a string from a IDataReader column when the value could be null
		/// </summary>
		private string getSqlString(IDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "null" : thisrdr.GetValue(thisord).ToString();
			//return (thisrdr.IsDBNull(thisord))? "" : thisrdr.GetString(thisord);
		}
		
		/// <summary>
		/// A safe method of getting a string from a IDataReader column when the value could be null
		/// </summary>
		private string getSqlInteger(IDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "null" : thisrdr.GetValue(thisord).ToString();
		}
		
		/// <summary>
		/// A safe method of getting a boolean result from a IDataReader column when the value could be null
		/// </summary>
		private string getSqlYesNo(IDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "no" : "yes";
		}
		
		/// <summary>
		/// A safe method of getting a guid string from a IDataReader column when the value could be null
		/// </summary>
		private string getSqlGuid(IDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "" : thisrdr.GetGuid(thisord).ToString();
		}
		
		#endregion
	}
}
